package com.ailk.kbi.kbirazor.util;
import groovy.util.logging.Slf4j;

import groovy.util.logging.Slf4j;

import groovy.xml.MarkupBuilder;

import com.ailk.kbi.kbirazor.vo.DimVO;
import com.ailk.kbi.kbirazor.vo.KpiVO;
import com.ailk.kbi.kbirazor.vo.ResultVO;
import com.ailk.kbi.kbirazor.vo.TimeRangeVO;
import com.ailk.kbi.kbirazor.vo.UserVO;

@Slf4j
class DBSqlUtil {
	//获得存在数据的时间范围
	TimeRangeVO getTimeRangeVO() {
		def sql = DspDBCP.getSql('db3')
		def tsql = /
		   select max(op_time) as maxTime,min(op_time) as minTime from ST_MINDMAP_KPI_DM 
		/
		def row = sql.firstRow(tsql)
		TimeRangeVO timeRangeVO = new TimeRangeVO(from:row.minTime,to:row.maxTime)
		sql.close()
		timeRangeVO
	}

	/**
	 //实例SQL：
	 //		select city_name,op_time,sum(kpiValue_2000003) as kpiValue_2000003,sum(kpiValue_2000005) as kpiValue_2000005
	 //		from (
	 //		select b.show_name as city_name,op_time ,
	 //		case when kpi_id =2000003 and division_flag = 1 then sum(kpi_value) when kpi_id = 2000003 and division_flag = 2 and sum(kpi_value2) <> 0 then sum(kpi_value1)*1.00 / sum(kpi_value2) when kpi_id = 2000003 and division_flag = 3 and sum(kpi_value2) <> 0 then sum(kpi_value1) * 100.00 / sum(kpi_value2)  else 0 end as kpiValue_2000003  ,0 as kpiValue_2000005
	 //							from ST_MINDMAP_KPI_DM a
	 //							left join DIM_PUB_COUNTY b on a.county_id = b.county_id
	 //							where
	 //							 op_time = '2011-10-07' and kpi_id = 2000003
	 //							group by op_time,b.show_name,kpi_id,division_flag
	 //		union all
	 //		select b.show_name as city_name,op_time,
	 //		0 as kpiValue_2000003,
	 //		case when kpi_id =2000005 and division_flag = 1 then sum(kpi_value) when kpi_id = 2000005 and division_flag = 2 and sum(kpi_value2) <> 0 then sum(kpi_value1)*1.00 / sum(kpi_value2) when kpi_id = 2000005 and division_flag = 3 and sum(kpi_value2) <> 0 then sum(kpi_value1) * 100.00 / sum(kpi_value2)  else 0 end as kpiValue_2000005
	 //							from ST_MINDMAP_KPI_DM a
	 //							left join DIM_PUB_COUNTY b on a.county_id = b.county_id
	 //							where
	 //							 op_time = '2011-10-07' and kpi_id = 2000005
	 //							group by op_time,b.show_name,kpi_id,division_flag ) t
	 //
	 //						group by op_time,city_name order by 1,2
	 */
	
	ResultVO getTimeDataList(String opTime,List<String> kpiIdList){
		def sql = DspDBCP.getSql('db3')

		String innerSql = ""
		String unionAllString = ""


		String sumSql=""
		String sumSqlLink = ""

		kpiIdList.each{ kpiId->
			def caseSql = "case when kpi_id =${kpiId} and division_flag = 1 then sum(kpi_value) when kpi_id = ${kpiId} and division_flag = 2 and sum(kpi_value2) <> 0 then sum(kpi_value1)*1.00 / sum(kpi_value2) when kpi_id = ${kpiId} and division_flag = 3 and sum(kpi_value2) <> 0 then sum(kpi_value1) * 100.00 / sum(kpi_value2)  else 0 end as kpiValue_${kpiId}"

			/*单独的KPI的值，需用0填充其他KPI，并保证KPI的顺序一致*/
			String kpiValueSql = ""
			kpiIdList.each{ otherKpiId->
				if(otherKpiId.equalsIgnoreCase(kpiId)){
					kpiValueSql = kpiValueSql + ", ${caseSql}"
				}
				else{
					kpiValueSql = kpiValueSql + ",0 as kpiValue_${otherKpiId}"
				}
			}
			def kpiSql = """
select op_time
${kpiValueSql}
from ST_MINDMAP_KPI_DM 
where
op_time between date('${opTime}')-30 day and '${opTime}' and kpi_id = ${kpiId}
group by op_time,kpi_id,division_flag
"""
			innerSql = innerSql + unionAllString + kpiSql
			unionAllString = """
 union all
			"""


			def sumKpiSql = "sum(kpiValue_${kpiId}) as kpiValue_${kpiId}"
			sumSql = sumSql + sumSqlLink + sumKpiSql
			sumSqlLink = " , "
		}

		def sqlString="""
select op_time,${sumSql}
from (
${innerSql}
) t
group by op_time order by 1
				"""

		log.info "时域分析数据信息：" + sqlString.toString()
		def writer = new StringWriter()
		def xml = new MarkupBuilder(writer)
		List jg = new ArrayList()
		xml.result() {
			sql.eachRow(sqlString as String) { row ->
				xml.data {
					xml.opTime(row.op_time.toString())
					kpiIdList.each{ kpiId->
						"kpiValue_${kpiId}"(row."kpiValue_${kpiId}")
					}

				}
			}
		}
		ResultVO result = new ResultVO(xmlString:writer.toString(), success:true)
		log.info "获取的时域数据："+ writer.toString()
		sql.close()
		result
	}
	
	ResultVO getBrandDataList(String opTime,List<String> kpiIdList){
		def sql = DspDBCP.getSql('db3')

		String innerSql = ""
		String unionAllString = ""


		String sumSql=""
		String sumSqlLink = ""

		kpiIdList.each{ kpiId->
			def caseSql = "case when kpi_id =${kpiId} and division_flag = 1 then sum(kpi_value) when kpi_id = ${kpiId} and division_flag = 2 and sum(kpi_value2) <> 0 then sum(kpi_value1)*1.00 / sum(kpi_value2) when kpi_id = ${kpiId} and division_flag = 3 and sum(kpi_value2) <> 0 then sum(kpi_value1) * 100.00 / sum(kpi_value2)  else 0 end as kpiValue_${kpiId}"

			/*单独的KPI的值，需用0填充其他KPI，并保证KPI的顺序一致*/
			String kpiValueSql = ""
			kpiIdList.each{ otherKpiId->
				if(otherKpiId.equalsIgnoreCase(kpiId)){
					kpiValueSql = kpiValueSql + ", ${caseSql}"
				}
				else{
					kpiValueSql = kpiValueSql + ",0 as kpiValue_${otherKpiId}"
				}
			}
			def kpiSql = """
select b.brand_name1,op_time
${kpiValueSql}
from ST_MINDMAP_KPI_DM a
left join dim_pub_brand1 b on a.brand_id1 = b.brand_id1
where
op_time = '${opTime}' and kpi_id = ${kpiId}
group by op_time,b.brand_name1,kpi_id,division_flag
"""
			innerSql = innerSql + unionAllString + kpiSql
			unionAllString = """
 union all
			"""


			def sumKpiSql = "sum(kpiValue_${kpiId}) as kpiValue_${kpiId}"
			sumSql = sumSql + sumSqlLink + sumKpiSql
			sumSqlLink = " , "
		}

		def sqlString="""
select brand_name1,${sumSql}
from (
${innerSql}
) t
group by brand_name1 order by 1
				"""

		log.info "品牌数据信息：" + sqlString.toString()
		def writer = new StringWriter()
		def xml = new MarkupBuilder(writer)
		List jg = new ArrayList()
		xml.result() {
			sql.eachRow(sqlString as String) { row ->
				data {
					brandName(row.brand_name1)
					kpiIdList.each{ kpiId->
						"kpiValue_${kpiId}"(row."kpiValue_${kpiId}")
					}

				}


			}
		}
		ResultVO result = new ResultVO(xmlString:writer.toString(), success:true)
		log.info writer.toString()
		sql.close()
		result
	}
	
	ResultVO areaList(DimVO dimVO,List<String> kpiIdList){
		def sql = DspDBCP.getSql('db3')

		String innerSql = ""
		String unionAllString = ""


		String sumSql=""
		String sumSqlLink = ""

		kpiIdList.each{ kpiId->
			def caseSql = "case when kpi_id =${kpiId} and division_flag = 1 then sum(kpi_value) when kpi_id = ${kpiId} and division_flag = 2 and sum(kpi_value2) <> 0 then sum(kpi_value1)*1.00 / sum(kpi_value2) when kpi_id = ${kpiId} and division_flag = 3 and sum(kpi_value2) <> 0 then sum(kpi_value1) * 100.00 / sum(kpi_value2)  else 0 end as kpiValue_${kpiId}"

			/*单独的KPI的值，需用0填充其他KPI，并保证KPI的顺序一致*/
			String kpiValueSql = ""
			kpiIdList.each{ otherKpiId->
				if(otherKpiId.equalsIgnoreCase(kpiId)){
					kpiValueSql = kpiValueSql + ", ${caseSql}"
				}
				else{
					kpiValueSql = kpiValueSql + ",0 as kpiValue_${otherKpiId}"
				}
			}
			def kpiSql = """
select b.area_name as city_name,'0000' as cityId,op_time
${kpiValueSql}
from ST_MINDMAP_KPI_DM a
left join DIM_PUB_AREA b on a.county_id = b.county_id
where
op_time = '${dimVO.opTime}' and kpi_id = ${kpiId} and a.county_id = ${dimVO.cityId}
group by op_time,b.area_name,kpi_id,division_flag
"""
			innerSql = innerSql + unionAllString + kpiSql
			unionAllString = """
 union all
			"""


			def sumKpiSql = "sum(kpiValue_${kpiId}) as kpiValue_${kpiId}"
			sumSql = sumSql + sumSqlLink + sumKpiSql
			sumSqlLink = " , "
		}

		def sqlString="""
select city_name,op_time,cityId,${sumSql}
from (
${innerSql}
) t
group by op_time,city_name,cityId order by 1,2
				"""

		log.info "地域下钻各分公司信息：" + sqlString.toString()
		def writer = new StringWriter()
		def xml = new MarkupBuilder(writer)
		List jg = new ArrayList()
		xml.result() {
			sql.eachRow(sqlString as String) { row ->
				data {
					cityName(row.city_name)
					cityId(row.cityId)
					opTime(row.op_time)
					kpiIdList.each{ kpiId->
						"kpiValue_${kpiId}"(row."kpiValue_${kpiId}")
					}

				}
			}
		}
		ResultVO result = new ResultVO(xmlString:writer.toString(), success:true)
		log.info "片区下钻数据："+writer.toString()
		sql.close()
		result
	}
	
	ResultVO cityList(DimVO dimVO,List<String> kpiIdList){
		def sql = DspDBCP.getSql('db3')

		String innerSql = ""
		String unionAllString = ""


		String sumSql=""
		String sumSqlLink = ""

		kpiIdList.each{ kpiId->
			def caseSql = "case when kpi_id =${kpiId} and division_flag = 1 then sum(kpi_value) when kpi_id = ${kpiId} and division_flag = 2 and sum(kpi_value2) <> 0 then sum(kpi_value1)*1.00 / sum(kpi_value2) when kpi_id = ${kpiId} and division_flag = 3 and sum(kpi_value2) <> 0 then sum(kpi_value1) * 100.00 / sum(kpi_value2)  else 0 end as kpiValue_${kpiId}"

			/*单独的KPI的值，需用0填充其他KPI，并保证KPI的顺序一致*/
			String kpiValueSql = ""
			kpiIdList.each{ otherKpiId->
				if(otherKpiId.equalsIgnoreCase(kpiId)){
					kpiValueSql = kpiValueSql + ", ${caseSql}"
				}
				else{
					kpiValueSql = kpiValueSql + ",0 as kpiValue_${otherKpiId}"
				}
			}
			def kpiSql = """
select b.show_name as city_name,a.county_id as cityId,op_time
${kpiValueSql}
from ST_MINDMAP_KPI_DM a
left join DIM_PUB_COUNTY b on a.county_id = b.county_id
where
op_time = '${dimVO.opTime}' and kpi_id = ${kpiId}
group by op_time,b.show_name,kpi_id,a.county_id,division_flag
"""
			innerSql = innerSql + unionAllString + kpiSql
			unionAllString = """
 union all
			"""


			def sumKpiSql = "sum(kpiValue_${kpiId}) as kpiValue_${kpiId}"
			sumSql = sumSql + sumSqlLink + sumKpiSql
			sumSqlLink = " , "
		}

		def sqlString="""
select city_name,cityId,op_time,${sumSql}
from (
${innerSql}
) t
group by op_time,city_name,cityId order by 1,2,3
				"""

		log.info "地域下钻各分公司信息：" + sqlString.toString()
		def writer = new StringWriter()
		def xml = new MarkupBuilder(writer)
		List jg = new ArrayList()
		xml.result() {
			sql.eachRow(sqlString as String) { row ->
				data {
					cityName(row.city_name)
					cityId(row.cityId)
					opTime(row.op_time)
					kpiIdList.each{ kpiId->
						"kpiValue_${kpiId}"(row."kpiValue_${kpiId}")
					}

				}


			}
		}
		ResultVO result = new ResultVO(xmlString:writer.toString(), success:true)
		sql.close()
		result
	}

	ResultVO getThresholdSetXML(List<String> kpiIds,UserVO userVO){
		def sql = DspDBCP.getSql('db3')
		def tsql
		String sb = kpiIds.join(',')
		tsql = /
			select a.kpi_id,b.kpi_name,a.min_rise_rate as minValue,a.max_rise_rate as maxValue
			from DIM_MINDMAP_WARNING_CFG a
			left join DIM_MINDMAP_KPI_CFG b on a.kpi_id = b.kpi_id
			where a.kpi_id in (${sb}) 
			and a.area_id = ${userVO.areaId} 
			and a.county_id = ${userVO.cityId}
			order by a.kpi_id 
		/
		log.info "获取阈值设置的kpiName：" + tsql
		def writer = new StringWriter()
		def xml = new MarkupBuilder(writer)
		List jg = new ArrayList()
		xml.result() {
			sql.eachRow(tsql as String) { row ->
				data(kpiId:row.kpi_id,kpiName:row.kpi_name,min:row.minValue,max:row.maxValue)
			}
		}
		ResultVO result = new ResultVO(xmlString:writer.toString(), success:true)
		log.info writer.toString()
		sql.close()
		result
	}
	
	ResultVO getDescDataXML(String condition){
		def sql = DspDBCP.getSql('db3')
		def tsql
		tsql = /
		select kpi_name,kpi_desc
		from DIM_MINDMAP_KPI_CFG 
		where 1=1
	/
		if(condition.equals("") || condition == null) {
			
		} else {
			tsql = tsql +  /
				 and kpi_name like '%${condition}%'
			/
		}
		tsql = tsql + /
			order by kpi_id 
		/
//		log.info "获取指标解释sql：" + tsql
		def writer = new StringWriter()
		def xml = new MarkupBuilder(writer)
		List jg = new ArrayList()
		xml.result() {
			sql.eachRow(tsql as String) { row ->
				data(kpiName:row.kpi_name,kpiDesc:row.kpi_desc)
			}
		}
		ResultVO result = new ResultVO(xmlString:writer.toString(), success:true)
//		log.info writer.toString()
		sql.close()
		result
	}
	
	//获取各kpi对应的kpiValue
	List<KpiVO> getKpiValues(String currentDate,UserVO userVO,List<String> kpiIds){
		log.info "取kpiValue"
		def sql = DspDBCP.getSql('db3')
		def tsql
		String sb = kpiIds.join(',')
		List<KpiVO> kpiVOes =[]
		if(kpiIds){
			log.info "kpi列表："+ sb.toString()

			if(userVO.cityId == "999") {

				tsql = /
					select '重庆市' city_name,op_time,kpi_id,sum(kpi_value1) as kpi_value1,sum(kpi_value2) as kpi_value2,sum(kpi_value) as kpi_value,sum(rise_value) as rise_value
					from ST_MINDMAP_KPI_DM 
					where kpi_id in (${sb}) 
					and op_time = '${currentDate}'
					group by kpi_id,op_time
				/;
			} else if(userVO.areaId == "999") {
				tsql = /
					select b.show_name as city_name,op_time,kpi_id,sum(kpi_value1) as kpi_value1,sum(kpi_value2) as kpi_value2,sum(kpi_value) as kpi_value,sum(rise_value) as rise_value
					from ST_MINDMAP_KPI_DM a
					left join DIM_PUB_COUNTY b on a.county_id = b.county_id
					where kpi_id in (${sb}) and a.county_id = ${userVO.cityId}
					and op_time = '${currentDate}'
					group by kpi_id,op_time,b.show_name
			/;
			} else {
				tsql = /
					select b.area_name as city_name,op_time,kpi_id,sum(kpi_value1) as kpi_value1,sum(kpi_value2) as kpi_value2,sum(kpi_value) as kpi_value,sum(rise_value) as rise_value
					from ST_MINDMAP_KPI_DM a
					left join DIM_PUB_AREA b on a.county_id = b.county_id and a.area_id = b.area_id
					where kpi_id in (${sb}) and a.county_id = ${userVO.cityId} and a.area_id = ${userVO.areaId}
					and op_time = '${currentDate}'
					group by kpi_id,op_time,b.area_name
			/;
			}

			log.info "获取kpiValues的sql语句：" + tsql

			sql.eachRow(tsql as String) { row ->
				kpiVOes.add(new KpiVO(cityId:userVO.cityId,areaId:userVO.areaId,cityName:row.city_name,opTime:row.op_time,kpiId:row.kpi_id, kpiValue1:filterValue(row.kpi_value1), kpiValue2:filterValue(row.kpi_value2), kpiValue:filterValue(row.kpi_value), riseValue:filterValue(row.rise_value)))
			}
		}
		sql.close()
		kpiVOes
	}

	def filterValue(obj){
		if(obj){
			return obj
		}
		return 0
	}
	
	int saveThresholdValue(String xmlStr,UserVO userVO) {
		def sql = DspDBCP.getSql('db3')
		int result = 0
		def kpiId
		def min
		def max
		def tsql
		def xml = new XmlParser().parseText(xmlStr)
		xml.data.each { data ->
			kpiId = data['@kpiId'] 
			min = data['@min'].toString().toDouble();
			max = data['@max'].toString().toDouble();
			log.info "kpiId:" + kpiId + ",min:" + min + ",max:" + max
			tsql = """
					update DIM_MINDMAP_WARNING_CFG 
					set MIN_RISE_RATE = ${min}, MAX_RISE_RATE = ${max} where KPI_ID = ${kpiId}
					and county_id =  ${userVO.cityId} and area_id = ${userVO.areaId}
			"""
			result = result + sql.executeUpdate(tsql)
			
		}
		result
	}
	
	//获取报警的kpiIds
	List<String> getWarnKpiIds(String time,UserVO userVO,List<String> kpiIds){
		log.info "取报警的kpiIds"
		def sql = DspDBCP.getSql('db3')
		def tsql
		String sb = kpiIds.join(',')
		List<String> warnKpiIds =[]
		if(kpiIds){
				tsql = /
					select distinct kpi_id 
					from ST_MINDMAP_WARNING_FLAG
					where kpi_id in (${sb})
					and op_time = '${time}' and county_id = ${userVO.cityId} and area_id = ${userVO.areaId}
					and warning_flag = 1
				/;

			log.info "获取报警kpiIds的sql语句：" + tsql

			sql.eachRow(tsql as String) { row ->
				warnKpiIds.add(row.kpi_id)
			}
		}
		sql.close()
		warnKpiIds
	}
}
